﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using System.Data.SqlClient;
using Public;

namespace DAL
{
    public class BatchDAL
    {
        DBConnection conn = new DBConnection();
        public DataSet LoadAllBatch()
        {
            return conn.LoadDBComBox("SELECT * FROM Batch");
        }

        public DataTable getTotalSpending(int batchId)
        {
            return conn.loadData("SELECT totalSpending FROM Batch WHERE batchId = " + batchId);
        }

        public DataTable SelectProductOfBatch(int batchId)
        {
            return conn.loadData("SELECT p.productId AS 'Product Id', p.barCode AS 'Barcode',(SELECT pn.productName FROM ProductName pn WHERE pn.proNameId = p.proNameId) AS 'Product name', " +
            "(SELECT pt.typeName FROM ProductType pt WHERE pt.typeId = (SELECT pn.typeId FROM ProductName pn WHERE pn.proNameId = p.proNameId)) AS 'Product type', " +
            "(SELECT u.unitName FROM Unit u WHERE u.unitId = p.unitId) AS Unit, " +
            "(SELECT b.brandName FROM Brand b WHERE b.brandId = p.brandId) AS Brand, " +
            "CONVERT(VARCHAR(10),p.expiredDate,103) AS 'Expired date', " +
            "p.quantity AS 'Current Quantity', bd.quantity AS 'Original Quality', p.unitPrice " +
            "FROM BatchDetails bd JOIN Product p ON bd.productId = p.productId " +
            "WHERE p.isShow = 1 AND bd.batchId=" + batchId);
        }

        public int AddProductBatchDetails(BatchDetails batchDetails)
        {
            int Nparameter = 10;
            string[] name = new string[Nparameter];
            object[] value = new object[Nparameter];
            name[0] = "@barCode"; value[0] = batchDetails.Product.BarCode;
            name[1] = "@brandId"; value[1] = batchDetails.Product.Brand.BrandId;
            name[2] = "@expiredDate"; value[2] = batchDetails.Product.ExpriredDate;
            name[3] = "@proNameId"; value[3] = batchDetails.Product.ProductName.PNameId;
            name[4] = "@quantity"; value[4] = batchDetails.Product.Quantity;
            name[5] = "@unitId"; value[5] = batchDetails.Product.Unit.UnitId;
            name[6] = "@unitPrice"; value[6] = batchDetails.Product.UnitPrice;
            name[7] = "@batchId"; value[7] = batchDetails.Batch.BatchId;
            name[8] = "@OQuantity"; value[8] = batchDetails.Quantity;
            name[9] = "@totalSpending"; value[9] = batchDetails.Batch.TotalSpending;
            return conn.Update("INSERT INTO Product(barCode, brandId, expiredDate, isShow, proNameId, quantity, unitId, " +
            "unitPrice, worth) VALUES(@barCode, @brandId, @expiredDate, 1, @proNameId, @quantity, @unitId, @unitPrice, 1) " +
            "INSERT INTO BatchDetails(batchId, productId, quantity)VALUES(@batchId ,@@IDENTITY, @OQuantity) " +
            "UPDATE Batch SET totalSpending = totalSpending + @totalSpending WHERE batchId=@batchId", name, value, Nparameter);
        }
        public int UpdateProductBatchDetails(BatchDetails batchDetails)
        {
            UpdateBatch(batchDetails);
            int Nparameter = 8;
            string[] name = new string[Nparameter];
            object[] value = new object[Nparameter];
            name[0] = "@batchId"; value[0] = batchDetails.Batch.BatchId;
            name[1] = "@brandId"; value[1] = batchDetails.Product.Brand.BrandId;
            name[2] = "@proNameId"; value[2] = batchDetails.Product.ProductName.PNameId;
            name[3] = "@unitId"; value[3] = batchDetails.Product.Unit.UnitId;
            name[4] = "@unitPrice"; value[4] = batchDetails.Product.UnitPrice;
            name[5] = "@OQuantity"; value[5] = batchDetails.Quantity;
            name[6] = "@expiredDate"; value[6] = batchDetails.Product.ExpriredDate;
            name[7] = "@productId"; value[7] = batchDetails.Product.ProductId;
            return conn.Update("UPDATE Product SET brandId=@brandId, proNameId=@proNameId, unitId=@unitId, " +
            "unitPrice=@unitPrice, expiredDate=@expiredDate WHERE productId=@productId UPDATE BatchDetails SET quantity=@OQuantity " +
            "WHERE batchId=@batchId AND productId=@productId", name, value, Nparameter);
        }
        private int UpdateBatch(BatchDetails batchDetails)
        {
            int Nparameter = 3;
            string[] name = new string[Nparameter];
            object[] value = new object[Nparameter];
            name[0] = "@productId"; value[0] = batchDetails.Product.ProductId;
            name[1] = "@totalSpending"; value[1] = batchDetails.Batch.TotalSpending;
            name[2] = "@batchId"; value[2] = batchDetails.Batch.BatchId;
            return conn.Update("UPDATE Batch SET totalSpending = totalSpending - " +
            "(SELECT ((SELECT quantity FROM BatchDetails WHERE productId = @productId)*unitPrice) AS total " +
            "FROM Product WHERE productId = @productId) + @totalSpending " +
            "WHERE batchId=@batchId", name, value, Nparameter);
        }

        public int AddBatchDetails(BatchDetails batchDetails)
        {
            int Nparameter = 10;
            string[] name = new string[Nparameter];
            object[] value = new object[Nparameter];
            name[0] = "@userId"; value[0] = batchDetails.Batch.UserId;
            name[1] = "@importedDate"; value[1] = batchDetails.Batch.ImportedDate;
            name[2] = "@totalSpending"; value[2] = batchDetails.Batch.TotalSpending;
            name[3] = "@barCode"; value[3] = batchDetails.Product.BarCode;
            name[4] = "@proName"; value[4] = batchDetails.Product.ProductName.PName;
            name[5] = "@brand"; value[5] = batchDetails.Product.Brand.BrandName;
            name[6] = "@unit"; value[6] = batchDetails.Product.Unit.UnitName;
            name[7] = "@unitPrice"; value[7] = batchDetails.Product.UnitPrice;
            name[8] = "@expiredDate"; value[8] = batchDetails.Product.ExpriredDate;
            name[9] = "@quantity"; value[9] = batchDetails.Product.Quantity;
            string query = "BEGIN TRANSACTION  DECLARE @BatchId int; " +
            "INSERT INTO Batch (userId, importedDate, totalSpending) VALUES(@userId,@importedDate,@totalSpending); " +
           "SELECT @BatchId = scope_identity(); " +
           "DECLARE @ProductId int; " +
           "INSERT INTO Product(barCode, proNameId, brandId, unitId, unitPrice, isShow, expiredDate, quantity, worth) " +
            "VALUES (@barCode, (SELECT pn.proNameId FROM ProductName pn WHERE pn.productName = @proName), " +
            " (SELECT brandId FROM Brand WHERE brandName = @brand), " +
            " (SELECT unitId FROM Unit WHERE unitName = @unit), @unitPrice,1,@expiredDate,@quantity,1); " +
            "   SELECT @ProductId = scope_identity(); " +
            "   INSERT INTO BatchDetails (batchId,productId,quantity) VALUES (@BatchId,@ProductId,@quantity) COMMIT";
            return conn.Update(query, name, value, Nparameter);
        }

        public DataTable getCurrentQuantity(int productId)
        {
            return conn.loadData("SELECT quantity FROM BatchDetails WHERE productId = " + productId);
        }

        public DataTable getImportedDate(int batchId)
        {
            return conn.loadData("SELECT importedDate FROM Batch WHERE batchId = " + batchId);
        }

        public DataTable getUserOfBatch(string batchId)
        {
            return conn.loadData("SELECT Batch.userId " +
            "FROM Batch WHERE Batch.batchId = " + batchId);
        }
    }
}
